﻿using Apps.Common;
using Apps.Models;
using Apps.Models.CustomModels;
using SqlSugar;
using System;
using System.CodeDom.Compiler;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using Wantn.FIS.Enum;

namespace Apps.DAL.SelfRcRelated
{
    public partial class LMS_TESTDATA_AUDIT_Repository : DbContext<YANX_RECORD>
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<TestDataOutPutModel> GetSugarList(TestDataQuertParaModel model, ref int totalCount, bool isPage = true)
        {
            var db = new DbContext<V_STATS_LMS_PLAN_RESULTModel>().Db;
            string SQL = $@"SELECT Distinct A.*,CES.CHESHU,G.MATCH_TIME,D.ITEM_VALUE AS YANX_VALUE,B.CustomMember AS AUDIT_USER_ID,C.SMALL,C.MEDIUM,C.LARGE,C.CRI,C.CSR,C.M40,C.M10,
                            C.JTXXLARGE,C.JTXLARGE,C.JTLARGE,C.JTMEDIUM,C.JTSMALL,E.CSR AS SELF_CSR,E.CRI AS SELF_CRI,E.YX_VALUE SELF_YANX_VALUE,E.ISJSUAN,E.ISRELATED,E.XJLM_CODE,E.XJLJT_CODE, COLLECT_TIME AS COLLECT_TIME
                            FROM V_STATS_LMS_PLAN_RESULT A 
                            LEFT JOIN V_FLOW_STEPINFO B ON A.AUDIT_ID = B.ID 
                            LEFT JOIN V_LMS_RESULT C ON A.TEST_CODE = C.TEST_CODE
                            LEFT JOIN (
	                            SELECT BB.BATCH_CODE,BB.ITEM_VALUE FROM (
	                            SELECT BATCH_CODE,MAX(ADDTIME) ADDTIME FROM YANX_RECORD GROUP BY BATCH_CODE) AA 
	                            INNER JOIN 
	                            YANX_RECORD BB ON AA.BATCH_CODE = BB.BATCH_CODE AND AA.ADDTIME = BB.ADDTIME AND AUDIT_STATUS = '审核通过'
                            ) D ON A.BATCH_CODE = D.BATCH_CODE
                            LEFT JOIN SELF_SAMPLING_RC_RELATED E ON E.RC_BATCH_CODE = A.BATCH_CODE
                            LEFT JOIN (SELECT COUNT(1) AS CHESHU,BATCH_CODE FROM V_RC_SAMPLING WHERE NVL(ISERROR,0) <> 1 GROUP BY BATCH_CODE) CES ON CES.BATCH_CODE = A.BATCH_CODE
                            LEFT JOIN (SELECT TEST_CODE,MIN(COLLECT_TIME) COLLECT_TIME FROM LMS_COLLECTSAMPLE GROUP BY TEST_CODE) F ON A.TEST_CODE = F.TEST_CODE
                            
                            LEFT JOIN V_LMS_QUERY G ON A.BATCH_CODE = G.BATCH_CODE

                            WHERE C.TEST_STATE = 1 AND C.TEST_TYPE_STR = '最终指标值' ";

            if (!string.IsNullOrEmpty(model.StartDate))
            {
                DateTime dStartDate = DateTime.Parse(model.StartDate);//.Date; 
                SQL += $" AND A.UPDATE_TIME >= TO_DATE('{dStartDate.ToString("yyyy-MM-dd 00:00:00")}', 'YYYY-MM-DD HH24:MI:SS') ";
            }
            if (!string.IsNullOrEmpty(model.EndDate))
            {
                DateTime dEndDate = DateTime.Parse(model.EndDate);//.Date.AddDays(1);
                SQL += $" AND A.UPDATE_TIME <= TO_DATE('{dEndDate.ToString("yyyy-MM-dd 23:59:59")}', 'YYYY-MM-DD HH24:MI:SS') ";
            }

            if (!string.IsNullOrEmpty(model.StartCollectTime))
            {
                DateTime dStartDate = DateTime.Parse(model.StartCollectTime);//.Date; 
                SQL += $" AND G.COLLECT_TIME >= TO_DATE('{dStartDate.ToString("yyyy-MM-dd 00:00:00")}', 'YYYY-MM-DD HH24:MI:SS') ";
            }
            if (!string.IsNullOrEmpty(model.EndCollectTime))
            {
                DateTime dEndDate = DateTime.Parse(model.EndCollectTime);//.Date.AddDays(1);
                SQL += $" AND G.COLLECT_TIME <= TO_DATE('{dEndDate.ToString("yyyy-MM-dd 23:59:59")}', 'YYYY-MM-DD HH24:MI:SS') ";
            }

            if (!string.IsNullOrEmpty(model.StartMatchTime))
            {
                DateTime dStartDate = DateTime.Parse(model.StartMatchTime);//.Date; 
                SQL += $" AND G.MATCH_TIME >= TO_DATE('{dStartDate.ToString("yyyy-MM-dd 00:00:00")}', 'YYYY-MM-DD HH24:MI:SS') ";
            }
            if (!string.IsNullOrEmpty(model.EndMatchTime))
            {
                DateTime dEndDate = DateTime.Parse(model.EndMatchTime);//.Date.AddDays(1);
                SQL += $" AND G.MATCH_TIME <= TO_DATE('{dEndDate.ToString("yyyy-MM-dd 23:59:59")}', 'YYYY-MM-DD HH24:MI:SS') ";
            }

            if (!string.IsNullOrEmpty(model.TestCode))
            {
                SQL += " AND A.TEST_CODE = @TestCode";
            }
            if (!string.IsNullOrEmpty(model.ISJSUAN))
            {
                if (model.ISJSUAN.Contains("=="))
                    model.ISJSUAN = "";
                if (model.ISJSUAN == "是")
                    SQL += " AND E.ISJSUAN = '是'";
                if (model.ISJSUAN == "否")
                    SQL += " AND NVL(E.ISJSUAN,'否') <> '是'";
            }
            if (!string.IsNullOrEmpty(model.ISRELATED))
            {
                if (model.ISRELATED.Contains("=="))
                    model.ISRELATED = "";
                if (model.ISRELATED == "是")
                    SQL += " AND E.ISRELATED = '是'";
                if (model.ISRELATED == "否")
                    SQL += " AND NVL(E.ISRELATED,'否') <> '是'";
            }
            if (!string.IsNullOrEmpty(model.BatchCode))
            {
                SQL += " AND A.BATCH_CODE = @BatchCode";
            }
            if (model.ApprovalStatus != -1)
            {
                SQL += " AND A.APPROVAL_STATUS = @ApprovalStatus";
            }

            if (!string.IsNullOrEmpty(model.BatchType))
            {
                SQL += " AND A.BATCH_TYPE = @BatchType";
            }
            if (!string.IsNullOrEmpty(model.MaterielName) && !model.MaterielName.Contains("=== 物料 ==="))
            {
                SQL += " AND A.MATERIEL_NAME = @MaterielName";
            }
            if (!string.IsNullOrEmpty(model.TypeStr))
            {
                SQL += " AND A.TYPE_STR = @TypeStr";
            }

            string SQLTOTAL = $@"SELECT COUNT(1) FROM (
								    %WHERER%
								) T ";
            SQLTOTAL = SQLTOTAL.Replace("%WHERER%", SQL);
            List<SugarParameter> sugarParameters = new List<SugarParameter>();
            sugarParameters.Add(new SugarParameter("@BatchType", model.BatchType));
            sugarParameters.Add(new SugarParameter("@TestCode", model.TestCode));
            sugarParameters.Add(new SugarParameter("@MaterielName", model.MaterielName));
            sugarParameters.Add(new SugarParameter("@TypeStr", model.TypeStr));
            sugarParameters.Add(new SugarParameter("@BatchCode", model.BatchCode));
            sugarParameters.Add(new SugarParameter("@ApprovalStatus", model.ApprovalStatus));

            totalCount = db.Ado.GetInt(SQLTOTAL, sugarParameters);
            model.OrderDate = string.IsNullOrEmpty(model.OrderDate) ? "UPDATE_TIME" : model.OrderDate;
            string SQLPAGE = $@"SELECT * FROM (
								    SELECT row_number() OVER (ORDER BY {model.OrderDate} desc) AS row_num,T.* FROM 
								    (
                                        %WHERER%
								    ) T ORDER BY {model.OrderDate} DESC
								)
								WHERE row_num BETWEEN {(model.PageIndex <= 1 ? 1 : (model.PageIndex - 1) * model.PageSize)} AND {model.PageIndex * model.PageSize} ";
            SQLPAGE = SQLPAGE.Replace("%WHERER%", SQL);
            if (isPage)
            {
                var data = db.Ado.GetDataTable(SQLPAGE, sugarParameters);
                return data.ToDataList<TestDataOutPutModel>();
            }
            else
            {
                var data = db.Ado.GetDataTable(SQL, sugarParameters);
                return data.ToDataList<TestDataOutPutModel>();
            }

        }

        /// <summary>
        /// 获取当前批次下面得所有车牌信息
        /// </summary>
        /// <param name="BatchCode"></param>
        /// <returns></returns>
        public List<V_RC_SAMPLING> GetChepai(string BatchCode)
        {
            var db = new DbContext<V_STATS_LMS_PLAN_RESULTModel>().Db;
            string SQL = "SELECT LPR FROM V_RC_SAMPLING WHERE BATCH_CODE = @BatchCode AND NVL(ISERROR,0) <> 1";
            List<SugarParameter> sugarParameters = new List<SugarParameter>();
            sugarParameters.Add(new SugarParameter("@BatchCode", BatchCode));
            var data = db.Ado.GetDataTable(SQL, sugarParameters);
            return data.ToDataList<V_RC_SAMPLING>();
        }

        /// <summary>
        /// 获取原始数据
        /// </summary>
        /// <param name="BatchCode"></param>
        /// <returns></returns>
        public List<V_LMS_TESTDATA_CONFIRMED> GetOriRecord(string testCode)
        {
            var db = new DbContext<V_STATS_LMS_PLAN_RESULTModel>().Db;
            string SQL = "SELECT * FROM V_LMS_TESTDATA_CONFIRMED_BK WHERE TEST_CODE = @TEST_CODE ORDER BY INDEX_ID ASC";
            List<SugarParameter> sugarParameters = new List<SugarParameter>();
            sugarParameters.Add(new SugarParameter("@TEST_CODE", testCode));
            var data = db.Ado.GetDataTable(SQL, sugarParameters);
            return data.ToDataList<V_LMS_TESTDATA_CONFIRMED>();
        }


        /// <summary>
        /// 备份审核前的数据
        /// </summary>
        /// <param name="testCode"></param>
        /// <returns></returns>
        public bool BeifenTestData(string testCode)
        {
            var db = new DbContext<LMS_TESTDATA_CONFIRMED>().Db;
            try
            {
                List<SugarParameter> parameters = new List<SugarParameter>();
                parameters.Add(new SugarParameter("@TEST_CODE", testCode));
                var list = db.Ado.GetDataTable("SELECT * FROM LMS_TESTDATA_CONFIRMED WHERE TEST_CODE = @TEST_CODE ", parameters);
                db.Ado.BeginTran();
                db.Ado.ExecuteCommand("DELETE FROM LMS_TESTDATA_CONFIRMED_BK WHERE TEST_CODE = @TEST_CODE", parameters);
                string SQL = "";
                for (int i = 0; i < list.Rows.Count; i++)
                {
                    var res = list.Rows[i];
                    SQL = @"INSERT INTO LMS_TESTDATA_CONFIRMED_BK 
                    (CONFIRMED_ID,STYPE,TEST_TYPE,TEST_MODE,TEST_CODE,INDEX_ID,TEST_VALUE,CONFIRMED_USER_ID,CONFIRMED_TIME,TEST_STATE,UPDATE_TIME,LABORATORY)
                        VALUES
                    (@CONFIRMED_ID,@STYPE,@TEST_TYPE,@TEST_MODE,@TEST_CODE,@INDEX_ID,@TEST_VALUE,@CONFIRMED_USER_ID,@CONFIRMED_TIME,@TEST_STATE,@UPDATE_TIME,@LABORATORY)";

                    parameters = new List<SugarParameter>();
                    parameters.Add(new SugarParameter("@CONFIRMED_ID", res["CONFIRMED_ID"].ToString()));
                    parameters.Add(new SugarParameter("@STYPE", ToInt(res["STYPE"].ToString())));
                    parameters.Add(new SugarParameter("@TEST_TYPE", ToInt(res["TEST_TYPE"].ToString())));
                    parameters.Add(new SugarParameter("@TEST_MODE", ToInt(res["TEST_MODE"].ToString())));
                    parameters.Add(new SugarParameter("@TEST_CODE", res["TEST_CODE"].ToString()));
                    parameters.Add(new SugarParameter("@INDEX_ID", res["INDEX_ID"].ToString()));
                    parameters.Add(new SugarParameter("@TEST_VALUE", ToDecimal(res["TEST_VALUE"].ToString())));
                    parameters.Add(new SugarParameter("@CONFIRMED_USER_ID", res["CONFIRMED_USER_ID"].ToString()));
                    parameters.Add(new SugarParameter("@CONFIRMED_TIME", ToDateTime(res["CONFIRMED_TIME"].ToString())));
                    parameters.Add(new SugarParameter("@TEST_STATE", ToInt(res["TEST_STATE"].ToString())));
                    parameters.Add(new SugarParameter("@UPDATE_TIME", ToDateTime(res["UPDATE_TIME"].ToString())));
                    parameters.Add(new SugarParameter("@LABORATORY", ToInt(res["LABORATORY"].ToString())));

                    db.Ado.ExecuteCommand(SQL, parameters);
                }
                db.Ado.CommitTran();
                return true;
            }
            catch (Exception ex)
            {
                db.Ado.RollbackTran();
                return false;
            }
        }

        /// <summary>
        /// 强制退回化验数据审核
        /// </summary>
        /// <param name="TestCode"></param>
        /// <returns></returns>
        public string TuiHuiAuditData(string TestCode)
        {
            var db = new DbContext<LMS_TESTDATA_CONFIRMED>().Db;
            try
            {
                if (string.IsNullOrEmpty(TestCode))
                    return "化验码异常";
                //SELECT * FROM LMS_TESTDATA_AUDIT WHERE TEST_CODE = '766671754'
                List<SugarParameter> parameters = new List<SugarParameter>();
                parameters.Add(new SugarParameter("@TEST_CODE", TestCode));
              var testData =   db.Ado.GetDataTable("SELECT APPROVAL_STATUS FROM LMS_TESTDATA_AUDIT WHERE TEST_CODE = @TEST_CODE ", parameters);
              if(testData==null)
                    return "化验码异常";
                if (testData.Rows[0][0] == null)
                    return "化验码异常";
                if (testData.Rows[0][0].ToString() != "3")
                    return "只能退回已经审核的数据";
                db.Ado.BeginTran();
               
                var list = db.Ado.GetDataTable("SELECT * FROM LMS_TESTDATA_CONFIRMED_BK WHERE TEST_CODE = @TEST_CODE ", parameters);

                string SQL = "SELECT ID,EXECUTION FROM FLOW_STEP WHERE ROWNUM = 1 AND SORT = 2";
                var stepData = db.Ado.GetDataTable(SQL);
                var execution = ToTbVal(stepData, "EXECUTION");
                var stepId = ToTbVal(stepData, "ID");
                if (string.IsNullOrEmpty(stepId))
                    return "stepId is null";
                if (string.IsNullOrEmpty(execution))
                    return "execution is null";

                SQL = $"SELECT ID FROM FLOW_FORMCONTENT WHERE ATTRZ = '{TestCode}' AND ROWNUM = 1";
                var contentId = ToTbVal(db.Ado.GetDataTable(SQL), "ID");
                if (string.IsNullOrEmpty(contentId))
                    return "contentId is null"; 

                SQL = $"SELECT ID FROM FLOW_FORMCONTENTSTEPCHECK WHERE CONTENTID = '{contentId}' AND STEPNO = 2 AND ROWNUM = 1";
                var stepcheckId = ToTbVal(db.Ado.GetDataTable(SQL), "ID");
                if (string.IsNullOrEmpty(stepcheckId))
                    return "stepcheckId is null";

                //FLOW_FORMCONTENTSTEPCHECK

                SQL = $"UPDATE FLOW_FORMCONTENTSTEPCHECKSTATE SET CHECKFLAG = 2, REAMRK = NULL,THESEAL = NULL WHERE STEPCHECKID = '{stepcheckId}'";
                db.Ado.ExecuteCommand(SQL);
                SQL = $"UPDATE FLOW_FORMCONTENTSTEPCHECK SET STATE = 2, STATEFLAG = 0 WHERE CONTENTID = '{contentId}' AND STEPID = '{stepId}'";
                db.Ado.ExecuteCommand(SQL);
                SQL = $"UPDATE FLOW_FORMCONTENT SET ATTRY = NULL, CUSTOMMEMBER = '{execution}' WHERE ATTRZ = '{TestCode}' ";
                db.Ado.ExecuteCommand(SQL);
                SQL = $"UPDATE LMS_TESTDATA_AUDIT SET APPROVAL_STATUS = 2 WHERE TEST_CODE = '{TestCode}'";
                db.Ado.ExecuteCommand(SQL);
                SQL = $"UPDATE LMS_TESTDATA_AUDIT_STATE SET AUDIT_STATE = 0 WHERE TEST_CODE = '{TestCode}'";
                db.Ado.ExecuteCommand(SQL);

                //清除之前错误设置为三级审核的数据
                //DELETE FROM FLOW_FORMCONTENTSTEPCHECKSTATE WHERE STEPCHECKID IN(SELECT ID FROM FLOW_FORMCONTENTSTEPCHECK WHERE STEPNO = 3)
                //DELETE FROM FLOW_FORMCONTENTSTEPCHECK WHERE STEPNO = 3

                //！！！！！！到底该不该先删除 再修改
                //先清空该化验编号 审核后的数据 然后在插入 审核前的数据
                //db.Ado.ExecuteCommand("DELETE FROM LMS_TESTDATA_CONFIRMED WHERE TEST_CODE = @TEST_CODE", parameters);


                for (int i = 0; i < list.Rows.Count; i++)
                {
                    var res = list.Rows[i];
                    SQL = @"UPDATE LMS_TESTDATA_CONFIRMED SET
                    STYPE=@STYPE,
                    TEST_TYPE=@TEST_TYPE,
                    TEST_MODE=@TEST_MODE,
                    TEST_CODE=@TEST_CODE,
                    INDEX_ID=@INDEX_ID,
                    TEST_VALUE=@TEST_VALUE,
                    CONFIRMED_USER_ID=@CONFIRMED_USER_ID,
                    CONFIRMED_TIME=@CONFIRMED_TIME,
                    TEST_STATE=@TEST_STATE,
                    UPDATE_TIME=@UPDATE_TIME,
                    LABORATORY=@LABORATORY
                    WHERE CONFIRMED_ID=@CONFIRMED_ID
                    ";

                    //！！！！！！到底该不该先删除 再修改
                    //SQL = @"INSERT INTO LMS_TESTDATA_CONFIRMED (CONFIRMED_ID,STYPE,TEST_TYPE,TEST_MODE,TEST_CODE,INDEX_ID,TEST_VALUE,CONFIRMED_USER_ID,CONFIRMED_TIME,TEST_STATE,UPDATE_TIME,LABORATORY)
                    //        VALUES
                    //        (@CONFIRMED_ID,@STYPE,TEST_TYPE,@TEST_MODE,@TEST_CODE,@INDEX_ID,@TEST_VALUE,@CONFIRMED_USER_ID,@CONFIRMED_TIME,@TEST_STATE,@UPDATE_TIME,@LABORATORY)
                    //";

                    parameters = new List<SugarParameter>();
                    parameters.Add(new SugarParameter("@CONFIRMED_ID", res["CONFIRMED_ID"].ToString()));
                    parameters.Add(new SugarParameter("@STYPE", ToInt(res["STYPE"].ToString())));
                    parameters.Add(new SugarParameter("@TEST_TYPE", ToInt(res["TEST_TYPE"].ToString())));
                    parameters.Add(new SugarParameter("@TEST_MODE", ToInt(res["TEST_MODE"].ToString())));
                    parameters.Add(new SugarParameter("@TEST_CODE", res["TEST_CODE"].ToString()));
                    parameters.Add(new SugarParameter("@INDEX_ID", res["INDEX_ID"].ToString()));
                    parameters.Add(new SugarParameter("@TEST_VALUE", ToDecimal(res["TEST_VALUE"].ToString())));
                    parameters.Add(new SugarParameter("@CONFIRMED_USER_ID", res["CONFIRMED_USER_ID"].ToString()));
                    parameters.Add(new SugarParameter("@CONFIRMED_TIME", ToDateTime(res["CONFIRMED_TIME"].ToString())));
                    parameters.Add(new SugarParameter("@TEST_STATE", ToInt(res["TEST_STATE"].ToString())));
                    parameters.Add(new SugarParameter("@UPDATE_TIME", ToDateTime(res["UPDATE_TIME"].ToString())));
                    parameters.Add(new SugarParameter("@LABORATORY", ToInt(res["LABORATORY"].ToString())));

                    db.Ado.ExecuteCommand(SQL, parameters);
                }
                db.Ado.CommitTran();
                return "";
            }
            catch
            {
                db.Ado.RollbackTran();
                return "error";
            }
        }

        public string ToTbVal(DataTable dataTable, string column)
        {
            try
            {
                return dataTable.Rows[0][column].ToString();
            }
            catch
            {
                return "";
            }
        }

        public int ToInt(string value)
        {
            try
            {
                int outResult = 0;
                int.TryParse(value, out outResult);
                return outResult;
            }
            catch
            {
                return 0;
            }
        }

        public decimal ToDecimal(string value)
        {

            try
            {
                decimal outResult = 0;
                decimal.TryParse(value, out outResult);
                return outResult;
            }
            catch
            {
                return 0;
            }
        }

        public DateTime ToDateTime(string value)
        {

            try
            {
                DateTime outResult = DateTime.Now;
                DateTime.TryParse(value, out outResult);
                return outResult;
            }
            catch
            {
                return DateTime.Now;
            }
        }
    }
}
